﻿using SpreadsheetGear;
using Sustainalytics.Alerts.AlertsEngine.Model;
using Sustainalytics.Alerts.Entities;
using Sustainalytics.ClientDW.Entities.Alerts;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text;

namespace Sustainalytics.Alerts.AlertsEngine
{
    class EmailBuilder
    {
        private static object _lockSync = new object();

        private readonly Color _redColor = Color.FromArgb(255, 0, 0);
        private readonly Color _greenColor = Color.FromArgb(0, 176, 80);
        private const string _subscriptionPeriodPlaceholder = "SUBSCRIPTION_PERIOD";
        private const string _companiesListPlaceholder = "COMPANIES_LIST";
        private const string _userFullNamePlaceholder = "USER_FULL_NAME";
        private const string _tickerFormat = "{0}:{1}";

        private string _emailBodyFile;
        private string _emailBodyFileNoAlerts;
        private string _emailSubjectFile;
        private string _controversyReportAddress;
        private string _senderEmail;
        private byte[] _alertsReportTemplate = null;
        private string _QAEmail;
        private string _subject;

        public EmailBuilder()
        {
            _alertsReportTemplate = File.ReadAllBytes(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "AlertsReportTemplate.xlsx"));
            _emailSubjectFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "AlertEmailSubject.txt");
            _senderEmail = ConfigurationManager.AppSettings["SenderEmail"];
            _controversyReportAddress = ConfigurationManager.AppSettings["ControversyReportAddress"];
            _QAEmail = (ConfigurationManager.AppSettings["QAEmail"] ?? string.Empty).Trim();
            _subject = File.ReadAllText(_emailSubjectFile);
            _emailBodyFile = File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "AlertEmailBody.htm"));
            _emailBodyFileNoAlerts = File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "AlertEmailBodyNoAlerts.htm"));
        }

        public Tuple<MailMessage, List<Guid>, Guid> GetMessageForSubscription(DateTime beginDate, DateTime endDate, string subscriptionType, SubscriptionDto subscription, 
            string connectionStringClientDw, List<Alert> filteredAlerts)
        {
            var subscriptionPeriod = GetFormattedPeriod(beginDate, endDate);

            var alertEmailMessage = new MailMessage
            {
                Subject = _subject.Replace(_subscriptionPeriodPlaceholder, subscriptionPeriod),
                IsBodyHtml = true,
                From = new MailAddress(_senderEmail)
            };
            alertEmailMessage.To.Add(new MailAddress(string.IsNullOrWhiteSpace(_QAEmail) ? subscription.UserEmail : _QAEmail));

            List<Guid> alertsIds = null;

            if (filteredAlerts.Count > 0)
            {
                var alertsReport = GetAlertsReport(beginDate, endDate, subscription, filteredAlerts);

                alertsIds = filteredAlerts.Select(a => a.Id).ToList();

                alertEmailMessage.Attachments.Add(new Attachment(alertsReport, string.Format("AlertsFor{0}.{1}", subscriptionPeriod.Replace(" ", string.Empty), "xlsx"), 
                    "application/vnd.ms-excel"));
            
                var companiesList = new StringBuilder();

                foreach (var a in filteredAlerts.Take(50))
                {
                    string controversyName = "";
                    string controversyNumber = "";
                    string updatedAssessment = "";

                    if (a.Data.Controversy != null)
                    {
                        controversyName = a.Data.Controversy.ControversyIndicatorName;
                        controversyNumber = a.Data.Controversy.ControversyIndicatorNumber;
                        updatedAssessment = a.Data.Controversy.UpdatedAssessment.Text;
                    }

                    if (a.Data.Outlook != null)
                    {
                        controversyName = a.Data.Outlook.ControversyIndicatorName;
                        controversyNumber = a.Data.Outlook.ControversyIndicatorNumber;
                        updatedAssessment = a.Data.Outlook.UpdatedAssessment.ToString();
                    }

                    var natureOfChangeColor = a.TypeOfChange == TypeOfChange.Positive ? _greenColor : _redColor;

                    // Start table row
                    companiesList.AppendLine("<tr>");

                    // Add company name column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Arial, sans-serif; color: #5f6061; border-bottom: 1px solid #cccccc;\">{0}</td>", a.CompanyAlert.Name));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    // Add type of change column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Arial, sans-serif; color: #5f6061; border-bottom: 1px solid #cccccc;\">{0}</td>", a.AlertType.ToString()));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    // Add level of importance column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Verdana, Arial, sans-serif; color: #f79647; border-bottom: 1px solid #cccccc;\">{0}</td>", new string('*', a.LevelOfImportance)));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    // Add nature of change column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Arial, sans-serif; color: #{0:X2}{1:X2}{2:X2}; border-bottom: 1px solid #cccccc;\">{3}</td>", natureOfChangeColor.R, natureOfChangeColor.G, natureOfChangeColor.B, a.TypeOfChange));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    // Add controversy indicator column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Arial, sans-serif; color: #5f6061; border-bottom: 1px solid #cccccc;\">{0}({1})</td>", controversyName, controversyNumber));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    // Add updated assessment column
                    companiesList.AppendLine(string.Format("<td style=\"font: normal 12px/15px Arial, sans-serif; color: #5f6061; border-bottom: 1px solid #cccccc;\">{0}</td>", updatedAssessment));
                    companiesList.AppendLine("<td width=\"0\" style=\"border-bottom: 1px solid #cccccc;\">&nbsp;</td>");

                    //End table row
                    companiesList.AppendLine("</tr>");

                    // Add spacing between lines
                    companiesList.AppendLine("<tr><td style=\"line-height: 5px;\">&nbsp;</td></tr>");
                }

                alertEmailMessage.Body = _emailBodyFile.Replace(_subscriptionPeriodPlaceholder, subscriptionType)
                    .Replace(_userFullNamePlaceholder, subscription.UserFullName).Replace(_companiesListPlaceholder, companiesList.ToString());
            }
            else
            {
                alertEmailMessage.Body = _emailBodyFileNoAlerts.Replace(_subscriptionPeriodPlaceholder, subscriptionType)
                    .Replace(_userFullNamePlaceholder, subscription.UserFullName);
            }

            return Tuple.Create(alertEmailMessage, alertsIds, subscription.UserId);
        }

        private string GetFormattedPeriod(DateTime beginDate, DateTime endDate)
        {
            return string.Format("{0:d MMMM yyyy} - {1:d MMMM yyyy}", beginDate, endDate);
        }

        private Stream GetAlertsReport(DateTime creationBeginDate,
                                DateTime creationEndDate,
                                SubscriptionDto subscription,
                                List<Alert> filteredAlerts)
        {
            const string dateRangeReference = "D3";
            const string portfolioReference = "D4";
            const string clientNameReference = "D5";
            const string copyrightReference = "C13";

            var companies = subscription.CompaniesWithPortfolios;

            var alertsWorkbook = GetFilledAlertsWorkbook(subscription.HasAccestToCompanyControversies, filteredAlerts);
            var coverWorksheet = alertsWorkbook.Worksheets["Cover"];
            var alertsWorksheet = alertsWorkbook.Worksheets["Alerts"];
            var currentRow = -(int)AlertsReportFields.StartRowNegativeIndex;

            coverWorksheet.Cells[dateRangeReference].Value = GetFormattedPeriod(creationBeginDate, creationEndDate);
            coverWorksheet.Cells[portfolioReference].Value = subscription.PortfolioNames;
            coverWorksheet.Cells[clientNameReference].Value = subscription.AccountName;

            var copyrightCell = coverWorksheet.Cells[copyrightReference];
            copyrightCell.Value = new System.Text.RegularExpressions.Regex(@"\d{4}").Replace(copyrightCell.Text, DateTime.Now.Year.ToString());

            foreach (var alert in filteredAlerts)
            {
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.PortfoliosIndex].Value = companies[alert.CompanyId];

                currentRow++;
            }

            SetGridLines(alertsWorksheet.Cells[0, (int)AlertsReportFields.TypeOfChangeIndex, currentRow - 1, (int)AlertsReportFields.PortfoliosIndex]);

            var memoryStream = new MemoryStream();
            lock (_lockSync)
            {
                alertsWorkbook.SaveToStream(memoryStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
            }
            memoryStream.Seek(0, SeekOrigin.Begin);

            return memoryStream;
        }

        private void SetGridLines(IRange reportRange)
        {
            reportRange.Borders[BordersIndex.EdgeTop].LineStyle =
                reportRange.Borders[BordersIndex.EdgeLeft].LineStyle =
                reportRange.Borders[BordersIndex.EdgeBottom].LineStyle =
                reportRange.Borders[BordersIndex.EdgeRight].LineStyle =
                reportRange.Borders[BordersIndex.InsideVertical].LineStyle =
                reportRange.Borders[BordersIndex.InsideHorizontal].LineStyle = LineStyle.Continuous;
            reportRange.Borders[BordersIndex.EdgeTop].Weight =
                reportRange.Borders[BordersIndex.EdgeLeft].Weight =
                reportRange.Borders[BordersIndex.EdgeBottom].Weight =
                reportRange.Borders[BordersIndex.EdgeRight].Weight =
                reportRange.Borders[BordersIndex.InsideVertical].Weight =
                reportRange.Borders[BordersIndex.InsideHorizontal].Weight = BorderWeight.Thin;
        }

        private IWorkbook GetFilledAlertsWorkbook(bool hasAccestToCompanyControversies, List<Alert> filteredAlerts)
        {
            IWorkbook alertsWorkbook;
            lock(_lockSync)
            {
                alertsWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(_alertsReportTemplate);
            }

            var alertsWorksheet = alertsWorkbook.Worksheets["Alerts"];
            var currentRow = -(int)AlertsReportFields.StartRowNegativeIndex;

            filteredAlerts = filteredAlerts
                                        .OrderByDescending(a => a.LevelOfImportance)
                                        .ThenBy(a => a.CompanyAlert.Name)
                                        .ToList();

            foreach (var alert in filteredAlerts)
            {
                var eventIndicatorData = new EventIndicatorData();
                var updatedAssessment = string.Empty;
                var previousAssessment = string.Empty;
                var summary = string.Empty;

                switch (alert.AlertType)
                {
                    case AlertChangeType.Controversy:
                        eventIndicatorData = alert.Data.Controversy as EventIndicatorData;
                        updatedAssessment = alert.Data.Controversy.UpdatedAssessment.Text;
                        previousAssessment = alert.Data.Controversy.PreviousAssessment.Text;
                        if (alert.Data.Controversy.UpdatedAssessment.Id > 2)// no summary should be visible if the new assessment is category 2 or less
                        {
                            summary = alert.Data.Controversy.EventControversySummary + Environment.NewLine + alert.Data.Controversy.EventACJustification;
                        }
                        break;
                    case AlertChangeType.Outlook:
                        eventIndicatorData = alert.Data.Outlook as EventIndicatorData;
                        updatedAssessment = alert.Data.Outlook.UpdatedAssessment.ToString();
                        previousAssessment = alert.Data.Outlook.PreviousAssessment.ToString();
                        summary = alert.Data.Outlook.OutlookText;
                        break;
                }

                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.TypeOfChangeIndex].Value = alert.AlertType.ToString();
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.ImportanceOfChangeIndex].Value = new string('*', alert.LevelOfImportance);

                var typeOfChangeCell = alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.NatureOfChangeIndex];
                typeOfChangeCell.Value = alert.TypeOfChange.ToString();
                typeOfChangeCell.Font.Color = alert.TypeOfChange == TypeOfChange.Positive ? _greenColor : _redColor;

                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.CompanyNameIndex].Value = alert.CompanyAlert.Name;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.CountryIndex].Value = alert.CompanyAlert.Country;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.SustainalyticsIndustryIndex].Value = alert.CompanyAlert.PeerGroup;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.CIQIDIndex].Value = alert.CompanyAlert.CIQID;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.TickerIndex].Value = String.Format(_tickerFormat, alert.CompanyAlert.ExchangeCode, alert.CompanyAlert.Ticker);
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.ThemeIndex].Value = eventIndicatorData.ControversyIndicatorTheme;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.ControversyIndicatorIndex].Value = eventIndicatorData.ControversyIndicatorName + Environment.NewLine + "(" + eventIndicatorData.ControversyIndicatorNumber + ")";
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.UpdatedAssessmentIndex].Value = updatedAssessment;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.PreviousAssessmentIndex].Value = previousAssessment;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.AlertRationaleIndex].Value = alert.Rationale;
                alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.SummaryIndex].Value = summary;
                var reportCell = alertsWorksheet.Cells[currentRow, (int)AlertsReportFields.ReportLinkIndex];
                if (hasAccestToCompanyControversies)
                {
                    PopulateCompanyLink(reportCell, $"{_controversyReportAddress}/{alert.CompanyAlert.UrlFriendlyName}/controversies/summary");
                }
                else
                {
                    reportCell.Value = string.Empty;
                }
                currentRow++;
            }

            return alertsWorkbook;
        }

        private void PopulateCompanyLink(IRange companyLinkCell, string companyReportPageLink)
        {
            companyLinkCell.Worksheet.Hyperlinks.Add(companyLinkCell, companyReportPageLink, null, companyReportPageLink, "Go to").Range.Font.Color = Color.FromArgb(0);
        }
    }
}
